create procedure gf_usp_ResourceAdd
(
	@Id int = null output,
	@Key nvarchar(500),
	@Value nvarchar(max),
	@Locale nvarchar(10)
)
as

insert into Resources([Key], Value, Locale)
values(@Key, @Value, @Locale)

set @Id = SCOPE_IDENTITY()

return @Id
go

create procedure gf_usp_ResourceUpdate
(
	@id int,
	@Key nvarchar(500),
	@Value nvarchar(max),
	@Locale nvarchar(10)
)
as
update Resources
set [Key] = @Key,
    Value = @Value,
    Locale = @Locale
where Id = @Id
go

create procedure gf_usp_ResourceDelete
(
	@Id int
)
as
delete from Resources
where Id = @Id
go

create procedure gf_usp_ResourceSelectAll
as
select Id, [Key], Value, Locale
from Resources
go

create procedure gf_usp_ResourceSelectOne
(
	@Id int
)
as
select Id, [Key], Value, Locale
from Resources
where Id = @Id
go

create procedure gf_usp_ResourceSelectAllByLocale
(
	@Locale nvarchar(10),
	@AppendDefault bit = 0,
	@DefaultLocale nvarchar(10) = null
)
as
begin
if @AppendDefault = 0
	select Id, [Key], Value, Locale
	from Resources
	where Locale = @Locale
else
	select Id, [Key], Value, Locale, CAST(1 as bit) as Translated
	from Resources
	where Locale = @Locale
	union
	select Id, [Key], Value, Locale, CAST(0 as bit) as Translated
	from Resources
	where Locale = @DefaultLocale and not [Key] in
	(select [Key]
	from Resources
	where Locale = @Locale)
end
create procedure gf_usp_ResourceGetByKeyAndLocale
(
	@Key nvarchar(500),
	@Locale nvarchar(10)
)
as
select Id, [Key], Value, Locale
from Resources
where [Key] = @Key and Locale = @Locale
go